require 'sqlite3'

class PlayerFactory	
	def initialize(first_names, last_names, states, positions, talent_profile_points)
		@distribution = Hash.new
		@cum_freq = Hash.new
		@cum_freq[:first_name] = first_names.map{|row| row['frequency']}.inject(0){|total, freq| total + freq}
	  @cum_freq[:last_name] = last_names.map{|row| row['frequency']}.inject(0){|total, freq| total + freq}
	  @cum_freq[:state] = states.map{|row| row['player_frequency']}.inject(0){|total, freq| total + freq}
		@cum_freq[:position] = positions.map{|row| row['player_frequency']}.inject(0){|total, freq| total + freq}
		@cum_freq[:grade] = talent_profile_points.map{|row| row['frequency']}.inject(0){|total, freq| total + freq}
		@distribution[:first_name] = first_names
		@distribution[:last_name] = last_names
		@distribution[:state] = states
		@distribution[:position] = positions
		@distribution[:grade] = talent_profile_points		 
	end	
	def create_player
		first_name_id = generate(:first_name)
		last_name_id = generate(:last_name)
		state_id = generate(:state)
		position_id = generate(:position)
		grade = generate(:grade)		
		Player.new(first_name_id, last_name_id, state_id, position_id, grade)
	end	
	def generate(attribute)
	 	# use frequency distribution to randomly generate an attribute
		# note that 0 frequency attributes are skipped
		pick = rand(@cum_freq[attribute])
		upper_bound = 0
		@distribution[attribute].each do |row|
			case attribute
				when :first_name, :last_name
				  upper_bound += row['frequency']
					return row['id'] if pick < upper_bound					
				when :state, :position
					upper_bound += row['player_frequency']
					return row['id'] if pick < upper_bound					
				when :grade
				  upper_bound += row['frequency']
					return row['grade'] if pick < upper_bound					
			end
		end
	end		 
end

class Player
	attr_reader :first_name_id, :last_name_id, :state_id, :position_id, :grade, :star	
	def initialize(first_name_id, last_name_id, state_id, position_id, grade, star)
		@first_name_id = first_name_id
		@last_name_id = last_name_id
		@state_id = state_id
		@position_id = position_id
		@grade = grade
		@star = star
	end
end

begin
  srand(117039506)
  
	db = SQLite3::Database.open("data.sqlite")
	db.results_as_hash = true
	
	first_names = db.execute("SELECT id, name, frequency FROM first_name")
	last_names = db.execute("SELECT id, name, frequency FROM last_name")
	states = db.execute("SELECT id, name, player_frequency FROM state")
	positions = db.execute("SELECT id, name, player_frequency FROM position")
	talent_profile_points = db.execute("SELECT grade, frequency FROM profile_point")
	
	factory = PlayerFactory.new(first_names, last_names, states, positions, talent_profile_points)
	
	3000.times do
		player = factory.create_player
		db.execute("INSERT into player (first_name_id, last_name_id, state_id, position_id, grade)
			VALUES (?, ?, ?, ?, ?)", player.first_name_id, player.last_name_id, player.state_id, player.position_id, player.grade)
	end
	
	# report out the players in human readable format
	file = File.open("generated_players.csv", "w")
	db.execute("SELECT id, first_name_id, last_name_id, state_id, position_id, grade FROM player") do |row|
		first_name = db.execute("SELECT name from first_name WHERE id = ?", row['first_name_id'])
		first_name = first_name[0]
		first_name = first_name['name']
		last_name = db.execute("SELECT name from last_name WHERE id = ?", row['last_name_id'])
		last_name = last_name[0]
		last_name = last_name['name']
		state = db.execute("SELECT name from state WHERE id = ?", row['state_id'])
		state = state[0]
		state = state['name']
		position = db.execute("SELECT name from position WHERE id = ?", row['position_id'])
		position = position[0]
		position = position['name']
		
		file.write(first_name + "," + last_name + "," + state + "," + position + "," + row['grade'].to_s + "\n") 
	end	
	
rescue SQLite3::Exception => e
	puts e
	
ensure
	db.close if db
end

